package persistence;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import persistence.exception.DAOException;
import persistence.util.JdbcUtil;

public class PurchaserDAOJdbcImpl extends JdbcDAOBase implements PurchaserDAO {

	private LibraryLocationDAO libraryLocationDAO;
	
	public PurchaserDAOJdbcImpl() {
		super();
	}	
	
	public LibraryLocationDAO getLibraryLocationDAO() {
		return libraryLocationDAO;
	}

	public void setLibraryLocationDAO(LibraryLocationDAO libraryLocationDAO) {
		this.libraryLocationDAO = libraryLocationDAO;
	}

	@Override
	public Purchaser find(int id) throws DAOException{
		String sql = "SELECT id, vorname, nachname, bibliotheksstandorte_id FROM besteller WHERE id = ?";
		
		Connection con = null;
		PreparedStatement prepStmt = null;
		ResultSet rs = null;
		Purchaser purchaser = null;
		LibraryLocation libraryLocation = null;
		
		try {
			con = getConProvider().getConnection();
			prepStmt = con.prepareStatement(sql);
			prepStmt.setInt(1, id);
			rs = prepStmt.executeQuery();
			
			if (rs.next()) {
				libraryLocation = libraryLocationDAO.find(rs.getInt(4));
				purchaser = new Purchaser (rs.getInt(1), rs.getString(2), rs.getString(3), libraryLocation);
			}
		}
		catch (SQLException se) {
			throw new DAOException("Problem when attempting to find Purchaser with id: " + id, se);
		}
		finally {
			JdbcUtil.cleanup(con, prepStmt, rs);
		}
		return purchaser;		
	}	

	@Override
	public List<Purchaser> findAll() throws DAOException {
		return findAllByLastName("");
	}

	@Override
	public List<Purchaser> findAllByLastName(String name) throws DAOException{
		String sql = "SELECT id, vorname, nachname, bibliotheksstandorte_id FROM besteller" +
					 " WHERE nachname LIKE ?";
		List<Purchaser> purchasers = new ArrayList<Purchaser>();
		LibraryLocation location = null;
		Connection con = null;
		PreparedStatement prepStmt = null;
		ResultSet rs = null;
		
		try {
			con = getConProvider().getConnection();
			prepStmt = con.prepareStatement(sql);
			prepStmt.setString(1, name+"%");
			rs = prepStmt.executeQuery();
			
			while (rs.next()) {
				location = libraryLocationDAO.find(rs.getInt(4));
				purchasers.add(new Purchaser(rs.getInt(1), rs.getString(2), rs.getString(3), location));
			}
		}
		catch (SQLException se) {
			throw new DAOException("Problem when attempting to find all Purchasers with lastname: '" + name + "'", se);
		}
		finally {
			JdbcUtil.cleanup(con, prepStmt, rs);
		}
		return purchasers;
	}
	
	@Override
	public List<Purchaser> findAllForLocation(LibraryLocation libraryLocation) throws DAOException{
		String sql = "SELECT id, vorname, nachname, bibliotheksstandorte_id FROM besteller" +
					 " WHERE bibliotheksstandorte_id = ?";
		List<Purchaser> purchasers = new ArrayList<Purchaser>();
		int libraryLocationId = libraryLocation.getId();
		Connection con = null;
		PreparedStatement prepStmt = null;
		ResultSet rs = null;
		
		try {
			con = getConProvider().getConnection();
			prepStmt = con.prepareStatement(sql);
			prepStmt.setInt(1, libraryLocationId);
			rs = prepStmt.executeQuery();
			
			while (rs.next()) {
				purchasers.add(new Purchaser(rs.getInt(1), rs.getString(2), rs.getString(3), libraryLocation));
			}
		}
		catch (SQLException se) {
			se.printStackTrace();
			throw new DAOException("Problem when attempting to find all purchasers for location with id: " + libraryLocationId, se);
		}
		finally {
			JdbcUtil.cleanup(con, prepStmt, rs);
		}
		return purchasers;		
	}

	@Override
	public void updatePurchaser(Purchaser purchaser) {
		// TODO Auto-generated method stub

	}

	@Override
	public Purchaser create() {
		// TODO Auto-generated method stub
		return null;
	}
}
